package com.delamer.facile.db;

import com.delamer.facile.obj.ObjectBack;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DBUtilBack {
    private static final String JDBC_URL = "jdbc:h2:mem:df_back";
    private static final String USERNAME = "yn89b5";
    private static final String PASSWORD = "9iqx7f";
    private static final String DRIVER = "org.h2.Driver";
    private static Connection connection = null;
    private static Statement statement = null;
    private static final String INIT = "CREATE TABLE IF NOT EXISTS df_back("
            + "user_name VARCHAR(50) NOT NULL,"
            + "world_name VARCHAR(50) NOT NULL,"
            + "location_x VARCHAR(50) NOT NULL,"
            + "location_y VARCHAR(50) NOT NULL,"
            + "location_z VARCHAR(50) NOT NULL,"
            + "create_time DATETIME,"
            + "id INT NOT NULL PRIMARY KEY AUTO_INCREMENT);";

    public static void init() {
        if (statement == null)
            try {
                open();
                statement.execute(INIT);
            } catch (Exception e) {
                e.printStackTrace();
            }

    }

    public static void open() {
        try {
            Class.forName(DRIVER);
            connection = DriverManager.getConnection(JDBC_URL, USERNAME, PASSWORD);
            statement = connection.createStatement();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void close() {
        try {
            statement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void close(Statement statement) {
        try {
            Connection connection = statement.getConnection();
            statement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static ResultSet select(String sql) {
        try {
            ResultSet resultSet = statement.executeQuery(sql);
            return resultSet;
        } catch (Exception e) {
            e.printStackTrace();
            close();
            return null;
        }
    }

    private static int insert(String sql) {
        try {
            int count = statement.executeUpdate(sql);
            return count;
        } catch (Exception e) {
            e.printStackTrace();
            close();
            return 0;
        }
    }

    private static int update(String sql) {
        try {
            int count = statement.executeUpdate(sql);
            return count;
        } catch (Exception e) {
            e.printStackTrace();
            close();
            return 0;
        }
    }

    private static int delete(String sql) {
        try {
            int count = statement.executeUpdate(sql);
            return count;
        } catch (Exception e) {
            e.printStackTrace();
            close();
            return 0;
        }
    }

    public static ObjectBack getBack(String userName) throws SQLException {
        ResultSet rs = select("SELECT * FROM df_back WHERE user_name = '" + userName + "';");
        List<ObjectBack> list = new ArrayList<ObjectBack>();
        if (rs != null)
            while (rs.next()) {
                ObjectBack object = new ObjectBack() {{
                    setUserName(rs.getString("user_name"));
                    setWorldName(rs.getString("world_name"));
                    setLocationX(rs.getString("location_x"));
                    setLocationY(rs.getString("location_y"));
                    setLocationZ(rs.getString("location_z"));
                    setCreateTime(rs.getDate("create_time"));
                }};
                list.add(object);
            }
        if (list.size() > 0)
            return list.get(0);
        return null;
    }

    public static boolean addBack(ObjectBack object) throws SQLException {
        String sql = "INSERT INTO df_back(user_name,world_name,location_x,location_y,location_z,create_time) VALUES("
                + "'" + object.getUserName() + "',"
                + "'" + object.getWorldName() + "',"
                + "'" + object.getLocationX() + "',"
                + "'" + object.getLocationY() + "',"
                + "'" + object.getLocationZ() + "',"
                + "CURRENT_DATE()"
                + ");";
        return insert(sql) > 0;
    }

    public static boolean delBack(String userName) {
        String sql = "DELETE FROM df_back WHERE user_name = '" + userName + "';";
        return delete(sql) > 0;
    }

    public static boolean setBack(ObjectBack object) throws SQLException {
        String sql = "UPDATE df_back SET "
                + "world_name = '" + object.getWorldName() + "',"
                + "location_x = '" + object.getLocationX() + "',"
                + "location_y = '" + object.getLocationY() + "',"
                + "location_z = '" + object.getLocationZ() + "',"
                + "create_time = CURRENT_DATE() "
                + "WHERE user_name = '" + object.getUserName() + "'"
                + ";";
        return update(sql) > 0;
    }
}
